from typing import Optional
from sqlmodel import and_, select, func, text
from fastapi import status
from .base import BaseService, QueryService
from app import models
from datetime import datetime


class HistoricalEurope18wanService(BaseService[models.MatchPredictEurope18wan]):
    """历史欧洲18万数据查询服务"""
    
    def __init__(self, db, current_user):
        super().__init__(db, current_user, models.MatchPredictEurope18wan)
    
    async def get_historical_europe_18wan(
        self, 
        payload: models.MatchPredictEurope18wanCondition
    ) -> models.MatchPredictEurope18wanResultResponse:
        """
        根据条件查询历史欧洲18万数据
        从 t_match_europe_18wan 表中查询符合条件的记录
        使用优雅的循环方式构建查询条件，支持范围查询
        """
        # 定义字段映射配置
        field_mappings = {
            # 文本字段
            "event_name": {
                "field": "event_name",
                "type": "text",
                "exact_match": True
            },
            "order_num": {
                "field": "order_num",
                "type": "text",
                "exact_match": True
            },
            "homesxname": {
                "field": "homesxname",
                "type": "text",
                "exact_match": False
            },
            "awaysxname": {
                "field": "awaysxname",
                "type": "text",
                "exact_match": False
            },
            "status": {
                "field": "status",
                "type": "text",
                "exact_match": True
            },
            
            # 数值字段
            "season_id": {
                "field": "season_id",
                "type": "exact"
            },
            "homeid": {
                "field": "homeid",
                "type": "exact"
            },
            "awayid": {
                "field": "awayid",
                "type": "exact"
            },
            "is_setting_range": {
                "field": "is_setting_range",
                "type": "exact"
            },
            
            # 日期字段 
            "ownerdate": {
                "field": "ownerdate",
                "type": "date",
                "start_field": "ownerdate_s",
                "end_field": "ownerdate_e"
            },
            "matchtime": {
                "field": "matchtime",
                "type": "date",
                "start_field": "matchtime_s",
                "end_field": "matchtime_e"
            },
            
            # 比分范围字段
            "homescore": {
                "field": "homescore",
                "type": "range",
                "min_field": "homescore_min",
                "max_field": "homescore_max"
            },
            "awayscore": {
                "field": "awayscore",
                "type": "range",
                "min_field": "awayscore_min",
                "max_field": "awayscore_max"
            },
            
            # 初始赔率范围字段
            "home_initial": {
                "field": "home_initial",
                "type": "range",
                "min_field": "home_initial_min",
                "max_field": "home_initial_max"
            },
            "draw_initial": {
                "field": "draw_initial",
                "type": "range",
                "min_field": "draw_initial_min",
                "max_field": "draw_initial_max"
            },
            "away_initial": {
                "field": "away_initial",
                "type": "range",
                "min_field": "away_initial_min",
                "max_field": "away_initial_max"
            }
        }
        
        # 统一异常处理
        try:
            # 构建查询条件
            conditions = self.build_conditions_from_payload(payload, field_mappings)
            
            # 执行分页查询
            result_data = await self.get_paginated_results(
                conditions=conditions,
                order_by_field=models.MatchPredictEurope18wan.matchtime,
                descending=False,
                pagination=payload
            )
            
            # 如果没有数据，返回404响应
            if result_data["total"] == 0:
                return models.MatchPredictEurope18wanResultResponse(
                    code=status.HTTP_404_NOT_FOUND,
                    message="没有找到匹配的欧洲18万数据",
                    data=None,
                    pagination=result_data["pagination"]
                )
            
            # 使用公共方法创建分页响应
            return self.create_paginated_response(
                result_data=result_data,
                response_class=models.MatchPredictEurope18wanResultResponse,
                message="检索成功",
                code=0
            )
        except Exception as e:
            # 其他系统错误
            return models.MatchPredictEurope18wanResultResponse(
                code=status.HTTP_500_INTERNAL_SERVER_ERROR,
                message=f"查询失败: {str(e)}",
                data=None,
                pagination=models.PaginationInfo(
                    current_page=payload.current_page,
                    page_size=payload.page_size,
                    total=0,
                    total_pages=0
                )
            )


class MatchesEurope18wMinMaxService(BaseService[models.MatchesEurope18wMinMax]):
    """欧洲18万数据最小最大值更新服务"""
    
    def __init__(self, db, current_user):
        super().__init__(db, current_user, models.MatchesEurope18wMinMax)
    
    async def update_matches_europe_18w_min_max(
        self, 
        payload: models.MatchesEurope18wMinMaxUpdateRequest
    ) -> models.MatchesEurope18wMinMaxResponse:
        """
        更新欧洲18万数据最小最大值表
        实现不存在则插入，存在则更新的功能
        主键是fid
        """
        try:
            # 如果传入的除了fid都为null,则直接返回
            if (payload.home_odds_min is None and payload.home_odds_max is None and 
                payload.draw_odds_min is None and payload.draw_odds_max is None and 
                payload.away_odds_min is None and payload.away_odds_max is None):
                return models.MatchesEurope18wMinMaxResponse(
                    code=0,
                    message="没有需要更新的数据",
                    data=None
                )
            
            # 查询是否已存在该fid的记录
            existing_record = self.db.exec(
                select(models.MatchesEurope18wMinMax).where(
                    models.MatchesEurope18wMinMax.fid == payload.fid
                )
            ).first()
            
            if existing_record:
                # 存在则更新
                # 更新非空字段
                if payload.home_odds_min is not None:
                    existing_record.home_odds_min = payload.home_odds_min
                if payload.home_odds_max is not None:
                    existing_record.home_odds_max = payload.home_odds_max
                if payload.draw_odds_min is not None:
                    existing_record.draw_odds_min = payload.draw_odds_min
                if payload.draw_odds_max is not None:
                    existing_record.draw_odds_max = payload.draw_odds_max
                if payload.away_odds_min is not None:
                    existing_record.away_odds_min = payload.away_odds_min
                if payload.away_odds_max is not None:
                    existing_record.away_odds_max = payload.away_odds_max
                
                self.db.add(existing_record)
                self.db.commit()
                self.db.refresh(existing_record)
                
                return models.MatchesEurope18wMinMaxResponse(
                    code=0,
                    message="更新成功",
                    data=existing_record
                )
            else:
                # 不存在则插入
                new_record = models.MatchesEurope18wMinMax(
                    fid=payload.fid,
                    home_odds_min=payload.home_odds_min,
                    home_odds_max=payload.home_odds_max,
                    draw_odds_min=payload.draw_odds_min,
                    draw_odds_max=payload.draw_odds_max,
                    away_odds_min=payload.away_odds_min,
                    away_odds_max=payload.away_odds_max
                )
                
                self.db.add(new_record)
                self.db.commit()
                self.db.refresh(new_record)
                
                return models.MatchesEurope18wMinMaxResponse(
                    code=0,
                    message="插入成功",
                    data=new_record
                )
                
        except Exception as e:
            self.db.rollback()
            return models.MatchesEurope18wMinMaxResponse(
                code=status.HTTP_500_INTERNAL_SERVER_ERROR,
                message=f"操作失败: {str(e)}",
                data=None
            )
    
    async def delete_matches_europe_18w_min_max(
        self, 
        fid: int
    ) -> models.MatchesEurope18wMinMaxResponse:
        """
        根据fid删除欧洲18万数据最小最大值表记录
        """
        try:
            # 查询是否已存在该fid的记录
            existing_record = self.db.exec(
                select(models.MatchesEurope18wMinMax).where(
                    models.MatchesEurope18wMinMax.fid == fid
                )
            ).first()
            
            if not existing_record:
                return models.MatchesEurope18wMinMaxResponse(
                code=0,
                message="删除成功",
                data=existing_record
            )
            
            # 删除记录
            self.db.delete(existing_record)
            self.db.commit()
            
            return models.MatchesEurope18wMinMaxResponse(
                code=0,
                message="删除成功",
                data=existing_record
            )
                
        except Exception as e:
            self.db.rollback()
            return models.MatchesEurope18wMinMaxResponse(
                code=status.HTTP_500_INTERNAL_SERVER_ERROR,
                message=f"删除失败: {str(e)}",
                data=None
            )

    async def get_matches_europe_18w_min_max(
            self, 
            fid: int
        ) -> models.MatchesEurope18wMinMaxResponse:
            """
            根据fid获得欧洲18万数据最小最大值表记录
            """
            try:
                # 查询是否已存在该fid的记录
                existing_record = self.db.exec(
                    select(models.MatchesEurope18wMinMax).where(
                        models.MatchesEurope18wMinMax.fid == fid
                    )
                ).first()
                
                if existing_record:
                    return models.MatchesEurope18wMinMaxResponse(
                    code=0,
                    message="成功",
                    data=existing_record
                )
                
                return models.MatchesEurope18wMinMaxResponse(
                    code=404,
                    message="没有设置最值,无法进行分析!",
                    data=None
                )
                    
            except Exception as e:
                self.db.rollback()
                return models.MatchesEurope18wMinMaxResponse(
                    code=status.HTTP_500_INTERNAL_SERVER_ERROR,
                    message=f"失败: {str(e)}",
                    data=None
                )


class HistoricalEurope29318wanService(BaseService[models.MatchPredictEurope29318wan]):
    """历史欧洲293-18万数据查询服务"""
    
    def __init__(self, db, current_user):
        super().__init__(db, current_user, models.MatchPredictEurope29318wan)
    
    async def get_historical_europe_293_18wan(
        self, 
        payload: models.MatchPredictEurope29318wanCondition
    ) -> models.MatchPredictEurope29318wanResultResponse:
        """
        根据条件查询历史欧洲293-18万数据
        从 t_293_18w 表中查询符合条件的记录
        使用优雅的循环方式构建查询条件，支持范围查询
        """
        # 定义字段映射配置
        field_mappings = {
            # 文本字段
            "event_name": {
                "field": "event_name",
                "type": "text",
                "exact_match": True
            },
            "homesxname": {
                "field": "homesxname",
                "type": "text",
                "exact_match": False
            },
            "awaysxname": {
                "field": "awaysxname",
                "type": "text",
                "exact_match": False
            },
            
            # 比分范围字段
            "homescore": {
                "field": "homescore",
                "type": "range",
                "min_field": "homescore_min",
                "max_field": "homescore_max"
            },
            "awayscore": {
                "field": "awayscore",
                "type": "range",
                "min_field": "awayscore_min",
                "max_field": "awayscore_max"
            },
            
            # 初始赔率范围字段
            "home_initial": {
                "field": "home_initial",
                "type": "range",
                "min_field": "home_initial_min",
                "max_field": "home_initial_max"
            },
            "draw_initial": {
                "field": "draw_initial",
                "type": "range",
                "min_field": "draw_initial_min",
                "max_field": "draw_initial_max"
            },
            "away_initial": {
                "field": "away_initial",
                "type": "range",
                "min_field": "away_initial_min",
                "max_field": "away_initial_max"
            },
            
            # 最终赔率范围字段
            "home_final": {
                "field": "home_final",
                "type": "range",
                "min_field": "home_final_min",
                "max_field": "home_final_max"
            },
            "draw_final": {
                "field": "draw_final",
                "type": "range",
                "min_field": "draw_final_min",
                "max_field": "draw_final_max"
            },
            "away_final": {
                "field": "away_final",
                "type": "range",
                "min_field": "away_final_min",
                "max_field": "away_final_max"
            }
        }
        
        # 统一异常处理
        try:
            # 构建查询条件
            conditions = self.build_conditions_from_payload(payload, field_mappings)
            
            # 执行分页查询
            result_data = await self.get_paginated_results(
                conditions=conditions,
                order_by_field=models.MatchPredictEurope29318wan.id,
                descending=False,
                pagination=payload
            )
            
            # 如果没有数据，返回404响应
            if result_data["total"] == 0:
                return models.MatchPredictEurope29318wanResultResponse(
                    code=status.HTTP_404_NOT_FOUND,
                    message="没有找到匹配的欧洲293-18万数据",
                    data=None,
                    pagination=result_data["pagination"]
                )
            
            # 使用公共方法创建分页响应
            return self.create_paginated_response(
                result_data=result_data,
                response_class=models.MatchPredictEurope29318wanResultResponse,
                message="检索成功",
                code=0
            )
        except Exception as e:
            # 其他系统错误
            return models.MatchPredictEurope29318wanResultResponse(
                code=status.HTTP_500_INTERNAL_SERVER_ERROR,
                message=f"查询失败: {str(e)}",
                data=None,
                pagination=models.PaginationInfo(
                    current_page=payload.current_page,
                    page_size=payload.page_size,
                    total=0,
                    total_pages=0
                )
            )


# 欧洲18万返回测试统计数据服务
class MatchPredictEurope18wReturnTestStatisticsService:
    """欧洲18万返回测试统计数据服务"""

    def __init__(self, db, current_user):
        self.db = db
        self.current_user = current_user

    async def get_match_predict_europe_18w_return_test_statistics(
        self,
        payload: models.MatchPredictEurope18wReturnTestStatisticsCondition
    ) -> models.MatchPredictEurope18wReturnTestStatisticsResponse:
        """
        根据条件查询欧洲18万返回测试统计数据
        使用原生SQL查询v_293_init_same_europe_final表的统计数据
        """
        try:
            # 构建动态SQL查询
            sql_query = """
                SELECT 
                    COUNT(*) AS total_matches,
                    SUM(CASE WHEN eo168.homescore > eo168.awayscore THEN 1 ELSE 0 END) AS home_wins,
                    SUM(CASE WHEN eo168.homescore = eo168.awayscore THEN 1 ELSE 0 END) AS draws,
                    SUM(CASE WHEN eo168.homescore < eo168.awayscore THEN 1 ELSE 0 END) AS away_wins,
                    ROUND(SUM(CASE WHEN eo168.homescore > eo168.awayscore THEN 1 ELSE 0 END) * 100.0 / NULLIF(COUNT(*), 0), 2) AS home_win_rate,
                    ROUND(SUM(CASE WHEN eo168.homescore = eo168.awayscore THEN 1 ELSE 0 END) * 100.0 / NULLIF(COUNT(*), 0), 2) AS draw_rate,
                    ROUND(SUM(CASE WHEN eo168.homescore < eo168.awayscore THEN 1 ELSE 0 END) * 100.0 / NULLIF(COUNT(*), 0), 2) AS away_win_rate
                FROM v_293_init_same_europe_final eo168 
                WHERE 1=1 and status='4'
            """
            
            # 构建参数字典和SQL条件子句
            params = {}
            
            # 赛事名称条件
            if payload.event_name is not None:
                sql_query += "\n    AND eo168.event_name = :event_name"
                params["event_name"] = payload.event_name

            # 初始赔率范围条件
            if payload.home_initial_min is not None or payload.home_initial_max is not None:
                if payload.home_initial_min is not None and payload.home_initial_max is not None:
                    # 有最小值和最大值
                    sql_query += "\n    AND eo168.home_initial BETWEEN :home_initial_min AND :home_initial_max"
                    params["home_initial_min"] = payload.home_initial_min
                    params["home_initial_max"] = payload.home_initial_max
                elif payload.home_initial_min is not None:
                    # 只有最小值
                    sql_query += "\n    AND eo168.home_initial >= :home_initial_min"
                    params["home_initial_min"] = payload.home_initial_min
                elif payload.home_initial_max is not None:
                    # 只有最大值
                    sql_query += "\n    AND eo168.home_initial <= :home_initial_max"
                    params["home_initial_max"] = payload.home_initial_max

            if payload.draw_initial_min is not None or payload.draw_initial_max is not None:
                if payload.draw_initial_min is not None and payload.draw_initial_max is not None:
                    # 有最小值和最大值
                    sql_query += "\n    AND eo168.draw_initial BETWEEN :draw_initial_min AND :draw_initial_max"
                    params["draw_initial_min"] = payload.draw_initial_min
                    params["draw_initial_max"] = payload.draw_initial_max
                elif payload.draw_initial_min is not None:
                    # 只有最小值
                    sql_query += "\n    AND eo168.draw_initial >= :draw_initial_min"
                    params["draw_initial_min"] = payload.draw_initial_min
                elif payload.draw_initial_max is not None:
                    # 只有最大值
                    sql_query += "\n    AND eo168.draw_initial <= :draw_initial_max"
                    params["draw_initial_max"] = payload.draw_initial_max

            if payload.away_initial_min is not None or payload.away_initial_max is not None:
                if payload.away_initial_min is not None and payload.away_initial_max is not None:
                    # 有最小值和最大值
                    sql_query += "\n    AND eo168.away_initial BETWEEN :away_initial_min AND :away_initial_max"
                    params["away_initial_min"] = payload.away_initial_min
                    params["away_initial_max"] = payload.away_initial_max
                elif payload.away_initial_min is not None:
                    # 只有最小值
                    sql_query += "\n    AND eo168.away_initial >= :away_initial_min"
                    params["away_initial_min"] = payload.away_initial_min
                elif payload.away_initial_max is not None:
                    # 只有最大值
                    sql_query += "\n    AND eo168.away_initial <= :away_initial_max"
                    params["away_initial_max"] = payload.away_initial_max

            # 赔率差值范围条件
            if payload.home_diff_min is not None or payload.home_diff_max is not None:
                if payload.home_diff_min is not None and payload.home_diff_max is not None:
                    # 有最小值和最大值
                    sql_query += "\n    AND eo168.home_diff BETWEEN :home_diff_min AND :home_diff_max"
                    params["home_diff_min"] = payload.home_diff_min
                    params["home_diff_max"] = payload.home_diff_max
                elif payload.home_diff_min is not None:
                    # 只有最小值
                    sql_query += "\n    AND eo168.home_diff >= :home_diff_min"
                    params["home_diff_min"] = payload.home_diff_min
                elif payload.home_diff_max is not None:
                    # 只有最大值
                    sql_query += "\n    AND eo168.home_diff <= :home_diff_max"
                    params["home_diff_max"] = payload.home_diff_max

            if payload.draw_diff_min is not None or payload.draw_diff_max is not None:
                if payload.draw_diff_min is not None and payload.draw_diff_max is not None:
                    # 有最小值和最大值
                    sql_query += "\n    AND eo168.draw_diff BETWEEN :draw_diff_min AND :draw_diff_max"
                    params["draw_diff_min"] = payload.draw_diff_min
                    params["draw_diff_max"] = payload.draw_diff_max
                elif payload.draw_diff_min is not None:
                    # 只有最小值
                    sql_query += "\n    AND eo168.draw_diff >= :draw_diff_min"
                    params["draw_diff_min"] = payload.draw_diff_min
                elif payload.draw_diff_max is not None:
                    # 只有最大值
                    sql_query += "\n    AND eo168.draw_diff <= :draw_diff_max"
                    params["draw_diff_max"] = payload.draw_diff_max

            if payload.away_diff_min is not None or payload.away_diff_max is not None:
                if payload.away_diff_min is not None and payload.away_diff_max is not None:
                    # 有最小值和最大值
                    sql_query += "\n    AND eo168.away_diff BETWEEN :away_diff_min AND :away_diff_max"
                    params["away_diff_min"] = payload.away_diff_min
                    params["away_diff_max"] = payload.away_diff_max
                elif payload.away_diff_min is not None:
                    # 只有最小值
                    sql_query += "\n    AND eo168.away_diff >= :away_diff_min"
                    params["away_diff_min"] = payload.away_diff_min
                elif payload.away_diff_max is not None:
                    # 只有最大值
                    sql_query += "\n    AND eo168.away_diff <= :away_diff_max"
                    params["away_diff_max"] = payload.away_diff_max

            # 执行原生SQL查询
            result = self.db.execute(text(sql_query), params)
            row = result.fetchone()
            
            # 将结果映射到模型
            statistics = models.MatchPredictEurope18wReturnTestStatistics(
                total_matches=row[0] if row[0] is not None else 0,
                home_wins=row[1] if row[1] is not None else 0,
                draws=row[2] if row[2] is not None else 0,
                away_wins=row[3] if row[3] is not None else 0,
                home_win_rate=row[4] if row[4] is not None else 0.0,
                draw_rate=row[5] if row[5] is not None else 0.0,
                away_win_rate=row[6] if row[6] is not None else 0.0
            )
            
            return models.MatchPredictEurope18wReturnTestStatisticsResponse(
                code=0,
                message="查询成功",
                data=statistics
            )
        except Exception as e:
            return models.MatchPredictEurope18wReturnTestStatisticsResponse(
                code=status.HTTP_500_INTERNAL_SERVER_ERROR,
                message=f"查询失败: {str(e)}",
                data=None
            )


# v_293_init_same_europe_final表查询服务
class V293InitSameEuropeFinalService(BaseService[models.V293InitSameEuropeFinal]):
    """v_293_init_same_europe_final表查询服务"""
    
    def __init__(self, db, current_user):
        super().__init__(db, current_user, models.V293InitSameEuropeFinal)
    
    async def get_same_europe_293_analysis(
        self, 
        payload: models.V293InitSameEuropeFinalCondition
    ) -> models.V293InitSameEuropeFinalResultResponse:
        """
        根据条件查询v_293_init_same_europe_final表的数据
        精确查询字段: event_name, fid
        范围查询字段: home_diff, draw_diff, away_diff
        """
        # 定义字段映射配置
        field_mappings = {
            # 精确查询字段
            "event_name": {
                "field": "event_name",
                "type": "text",
                "exact_match": True
            },
            "status": {
                "field": "status",
                "type": "text",
                "exact_match": True
            },
            "fid": {
                "field": "fid",
                "type": "exact"
            },
            
            
            # 范围查询字段
            "home_initial": {
                "field": "home_initial",
                "type": "range",
                "min_field": "home_initial_min",
                "max_field": "home_initial_max"
            },
            "draw_initial": {
                "field": "draw_initial",
                "type": "range",
                "min_field": "draw_initial_min",
                "max_field": "draw_initial_max"
            },
            "away_initial": {
                "field": "away_initial",
                "type": "range",
                "min_field": "away_initial_min",
                "max_field": "away_initial_max"
            },
            "home_diff": {
                "field": "home_diff",
                "type": "range",
                "min_field": "home_diff_min",
                "max_field": "home_diff_max"
            },
            "draw_diff": {
                "field": "draw_diff",
                "type": "range",
                "min_field": "draw_diff_min",
                "max_field": "draw_diff_max"
            },
            "away_diff": {
                "field": "away_diff",
                "type": "range",
                "min_field": "away_diff_min",
                "max_field": "away_diff_max"
            }
        }

        # 统一异常处理
        try:
            # 先初始化条件列表
            conditions = []
            
            # 先检查 fid 是否在 payload 中，如果存在则直接构建条件
            if hasattr(payload, 'fid') and payload.fid is not None:
                conditions.append(getattr(self.model, 'fid') == payload.fid)
            
            # 再通过字段映射配置构建其他查询条件
            additional_conditions = self.build_conditions_from_payload(payload, field_mappings)
            conditions.extend(additional_conditions)
            
            # 执行分页查询
            result_data = await self.get_paginated_results(
                conditions=conditions,
                order_by_field=models.V293InitSameEuropeFinal.matchtime,
                descending=True,
                pagination=payload
            )
            
            # 如果没有数据，返回404响应
            if result_data["total"] == 0:
                return models.V293InitSameEuropeFinalResultResponse(
                    code=status.HTTP_404_NOT_FOUND,
                    message="没有找到匹配的欧洲293分析数据",
                    data=None,
                    pagination=result_data["pagination"]
                )
            
            # 使用公共方法创建分页响应
            return self.create_paginated_response(
                result_data=result_data,
                response_class=models.V293InitSameEuropeFinalResultResponse,
                message="检索成功",
                code=0
            )
        except Exception as e:
            # 其他系统错误
            return models.V293InitSameEuropeFinalResultResponse(
                code=status.HTTP_500_INTERNAL_SERVER_ERROR,
                message=f"查询失败: {str(e)}",
                data=None,
                pagination=models.PaginationInfo(
                    current_page=payload.current_page,
                    page_size=payload.page_size,
                    total=0,
                    total_pages=0
                )
            )
